'''
Author: duliang thinktanker@163.com
Date: 2025-07-04 14:48:25
LastEditors: duliang thinktanker@163.com
LastEditTime: 2025-10-10 20:35:15
FilePath: 
Description: 点餐统计系统模块，用于处理点餐数据统计、值班表查询、网络状态检测等功能
'''

import sqlite3
import time
import requests
import json
from datetime import datetime, timedelta
import pandas as pd
from threading import Thread
from collections import Counter
import wechatsubscribe


def get_allnames():
    '''
    获取所有点餐人员姓名列表
    :return: list - 包含所有不重复姓名的列表
    '''
    conn = sqlite3.connect('./data/diancan.db')
    cursor = conn.cursor()
    query = "SELECT DISTINCT name FROM diancan"
    cursor.execute(query)
    # 获取结果
    unique_names = [row[0] for row in cursor.fetchall()]

    conn.close()
    return unique_names


def tongji(start, end):
    '''
    统计指定时间范围内的点餐数据
    :param start: int - 开始时间戳
    :param end: int - 结束时间戳
    :return: list - 点餐统计结果列表
    '''
    conn = sqlite3.connect('./data/diancan.db')
    cursor = conn.cursor()
    query = "SELECT diancan,time,COUNT(*) FROM diancan WHERE time >= ? AND time <= ? GROUP BY diancan"
    cursor.execute(query, (start, end))
    results = cursor.fetchall()

    conn.close()
    return results


def check_network():
    '''
    检测网络状态并定时保存到数据库
    每60秒检测一次网络连接状态
    '''
    print("检测网络状态")
    while 1:
        try:
            print(
                f"{time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))} 网络波动：",
                save_network_status(),
                end='\r')
        except Exception as e:
            print(f"Network check failed: {e}")
        time.sleep(60)


def save_network_status():
    '''
    保存网络状态到数据库
    当网络不通时尝试关闭以太网接口
    :return: bool - 状态是否变更
    '''
    conn = sqlite3.connect('./data/diancan.db')
    cursor = conn.cursor()
    urls = ['https://www.baidu.com/', 'https://www.jd.com']

    def check_url(url):
        try:
            # 从URL中提取主机名
            from urllib.parse import urlparse
            parsed_url = urlparse(url)
            host = parsed_url.netloc

            # 使用ping命令检测连通性
            import subprocess
            if subprocess.run(["ping", "-n", "5", "-w", "3000", host],
                              stdout=subprocess.DEVNULL,
                              stderr=subprocess.DEVNULL).returncode == 0:
                return True
            else:
                return False
        except Exception as e:
            print(f"\n检查 {url} 时出错: {e}")
            return False

    try:
        # 使用多线程检测所有URL
        from concurrent.futures import ThreadPoolExecutor
        with ThreadPoolExecutor(max_workers=len(urls)) as executor:
            results = list(executor.map(check_url, urls))

        # 只要有一个URL通畅时，status为1
        # status = 1 if all(results) else 0
        status = 1 if any(results) else 0

        # print(status, response.status_code)
        cursor.execute(
            "CREATE TABLE IF NOT EXISTS network_status (status INTEGER, timestamp INTEGER)"
        )

        # 查询最近的一条状态记录
        cursor.execute(
            "SELECT status FROM network_status ORDER BY timestamp DESC LIMIT 1"
        )
        last_status = cursor.fetchone()
        # print(last_status)
        # 如果最近状态存在且与新状态相同，则不处理
        if last_status and last_status[0] == status:
            # conn.close()
            return False

        # 否则插入新状态
        print("网络状态变更")
        cursor.execute(
            "INSERT INTO network_status (status, timestamp) VALUES (?, ?)",
            (status, int(time.time())))

    except Exception as e:
        print('网络状态异常：', e)
        status = 0
        cursor.execute(
            "INSERT INTO network_status (status, timestamp) VALUES (?, ?)",
            (status, int(time.time())))
    finally:
        conn.commit()
        conn.close()

    # 如果网络不通，则关闭以太网
    if status == 0:
        import subprocess
        try:
            # Windows系统命令
            subprocess.run(
                ["netsh", "interface", "set", "interface", "以太网", "disabled"],
                check=True,
                stdout=subprocess.DEVNULL,
                stderr=subprocess.DEVNULL)
            print("以太网已关闭")
        except subprocess.CalledProcessError:
            try:
                # 如果是中文接口名失败，尝试英文接口名
                subprocess.run([
                    "netsh", "interface", "set", "interface", "Ethernet",
                    "disabled"
                ],
                               check=True,
                               stdout=subprocess.DEVNULL,
                               stderr=subprocess.DEVNULL)
                print("Ethernet已关闭")
            except subprocess.CalledProcessError:
                print("关闭以太网失败")
        except FileNotFoundError:
            try:
                # Linux/macOS系统命令
                subprocess.run(["sudo", "ifconfig", "eth0", "down"],
                               check=True,
                               stdout=subprocess.DEVNULL,
                               stderr=subprocess.DEVNULL)
                print("eth0已关闭")
            except subprocess.CalledProcessError:
                print("关闭eth0失败")
            except FileNotFoundError:
                print("未找到合适的网络管理命令")

    return True


def history():
    '''
    从MSG1.db数据库中读取历史聊天记录
    '''
    conn = sqlite3.connect('D:\\lljdata\\MSG1.db')
    cursor = conn.cursor()
    query = 'SELECT * FROM MSG1 WHERE StrTalker="19460744006@chatroom"'
    cursor.execute(query, (0, 1751600010))
    results = cursor.fetchall()
    conn.close()


def time_to_timestamp(hour=00, minute=00, second=00, step=0):
    '''
    将指定时间转换为时间戳
    :param hour: int - 小时 (默认0)
    :param minute: int - 分钟 (默认0)
    :param second: int - 秒 (默认0)
    :param step: int - 天数步长 (默认0，表示当天)
    :return: int - 目标时间戳
    '''
    next_time = 86400 * step
    now_time = int(time.time()) + next_time
    time_str = time.strftime(f"%Y-%m-%d {hour}:{minute}:{second}",
                             time.localtime(now_time))
    date_object = datetime.strptime(time_str, "%Y-%m-%d %H:%M:%S")
    timestamp = int(date_object.timestamp())
    return timestamp


def date_to_timestamp(date_str, hour=0, minute=0, second=0):
    '''
    将指定日期字符串转换为时间戳
    :param date_str: str - 日期字符串 (格式：YYYY-MM-DD)
    :param hour: int - 小时 (默认0)
    :param minute: int - 分钟 (默认0)
    :param second: int - 秒 (默认0)
    :return: int - 目标时间戳
    '''
    date_object = datetime.strptime(str(date_str), "%Y-%m-%d")
    # date_object = datetime.strftime(date_str, "%Y-%m-%d")
    date_object = date_object.replace(hour=hour, minute=minute, second=second)
    timestamp = int(date_object.timestamp())
    return timestamp


def get_start_and_end_of_day(now_time):
    '''
    获取指定日期的开始和结束时间戳 (00:00:00 和 23:59:59)
    :param now_time: int - 指定时间戳
    :return: tuple - (开始时间戳, 结束时间戳)
    '''
    # 将时间戳转换为datetime对象
    dt = datetime.fromtimestamp(now_time)

    # 构建当天0时0分0秒的datetime对象
    dt_0 = dt.replace(hour=0, minute=0, second=0, microsecond=0)

    # 构建当天23时59分59秒的datetime对象
    dt_23_59_59 = dt.replace(hour=23, minute=59, second=59, microsecond=0)

    # 转换为时间戳
    timestamp_0 = int(dt_0.timestamp())
    timestamp_23_59_59 = int(dt_23_59_59.timestamp())

    return timestamp_0, timestamp_23_59_59


def get_dates_between_timestamps(start_time, end_time):
    """
    获取两个时间戳之间的所有日期
    :param start_time: int - 起始时间戳（秒）
    :param end_time: int - 结束时间戳（秒）
    :return: list - 日期字符串列表（格式：YYYY-MM-DD）
    """
    # 确保开始时间小于等于结束时间
    if start_time > end_time:
        start_time, end_time = end_time, start_time

    # 转换为datetime对象
    start_date = datetime.fromtimestamp(start_time)
    end_date = datetime.fromtimestamp(end_time)

    # 清除时间部分，只保留日期
    start_date = start_date.replace(hour=0, minute=0, second=0, microsecond=0)
    end_date = end_date.replace(hour=0, minute=0, second=0, microsecond=0)

    # 计算日期差
    delta = end_date - start_date

    # 生成日期列表
    dates = []
    for i in range(delta.days + 1):
        date_str = (start_date + timedelta(days=i)).strftime("%Y年%m月%d日")
        dates.append(date_str)

    return dates


def yeban():
    '''
    获取夜班点餐信息
    根据当前时间判断需要查询的时间段，并返回相应点餐记录
    :return: str - 点餐记录字符串
    '''
    now_time = int(time.time())
    # 将datetime对象转换为时间戳
    timestamp_15 = time_to_timestamp(hour=15)
    timestamp_before_15 = time_to_timestamp(hour=15, step=-1)
    timestamp_before_23 = time_to_timestamp(hour=23, step=-1)
    timestamp_9 = time_to_timestamp(hour=9)
    timestamp_8 = time_to_timestamp(hour=8)
    # timestamp_0 = int(date_object_23.timestamp())
    conn = sqlite3.connect('./data/diancan.db')
    # print(timestamp_16, timestamp_9, timestamp_0, now_time)
    rf = ''
    if now_time > timestamp_15:
        print('晚')
        # timestamp = timestamp_16
        sqltxt = '''SELECT name,diancan,time FROM diancan WHERE time>? AND (diancan LIKE "%晚%" or diancan LIKE "%早%") ORDER BY TIME DESC'''
        rf = conn.execute(sqltxt, (timestamp_15, ))
    elif now_time < timestamp_9:
        print('早')
        sqltxt = '''SELECT name,diancan,time FROM diancan WHERE time>? AND time<? AND (diancan LIKE "%晚%" or diancan LIKE "%早%") ORDER BY TIME DESC'''
        rf = conn.execute(sqltxt, (
            timestamp_before_15,
            timestamp_8,
        ))
    if rf:
        rf = str(rf.fetchall())
    else:
        rf = 'error'
    conn.close()
    return rf


def currentDay(start=None, end=None):
    '''
    获取当前日点餐信息
    :param start: int - 开始时间戳（可选）
    :param end: int - 结束时间戳（可选）
    :return: tuple - (点餐记录列表, 值班表, 所有人员列表)
    '''
    conn = sqlite3.connect('./data/diancan.db')
    result = []
    zhiban = []
    repeatedly = []
    if start and end:
        '''
        指定时间
        '''
        days = get_dates_between_timestamps(start, end)
        for day in days:
            zhiban.append([day, zhibanbiao(day)])
        sqltxt = '''SELECT name,diancan,time FROM diancan WHERE time>? AND time<? AND (diancan LIKE "%晚%" or diancan LIKE "%早%" or diancan LIKE "%午%") ORDER BY TIME DESC'''
        rf = conn.execute(sqltxt, (
            start,
            end,
        ))
        # result = rf.fetchall()
    else:
        '''
        默认当日
        '''
        now_time = int(time.time())
        # zhiban = zhibanbiao(now_time)
        zhiban = zhibanbiao(
            time.strftime("%Y年%m月%d日", time.localtime(time.time())))
        start_time, end_time = get_start_and_end_of_day(now_time)
        sqltxt = '''SELECT name,diancan,time FROM diancan WHERE time>? AND time<? AND (diancan LIKE "%晚%" or diancan LIKE "%早%" or diancan LIKE "%午%") ORDER BY TIME DESC'''
        rf = conn.execute(sqltxt, (
            start_time,
            end_time,
        ))
    result = rf.fetchall()
    conn.close()
    if result:

        #  提取前两个字段（人名, 餐别）
        pairs = [(name, meal) for name, meal, _ in result]

        # 统计每个 (name, meal) 组合的出现次数
        counter = Counter(pairs)

        # 找出重复出现（≥2次）的组合
        duplicates = {
            pair: count
            for pair, count in counter.items() if count >= 2
        }

        # 输出结果
        print("重复的 (人名, 餐别) 组合：")
        for (name, meal), count in duplicates.items():
            print(f"  {name}, {meal} -> 出现 {count} 次")
            nickname = getNicknameByRealname(name)
            repeatedly.append((nickname, meal, count))
        realname_list = list(set([r[0] for r in result]))
        # print(realname_list)
        for realname in realname_list:
            nickname = getNicknameByRealname(realname)
            for r in result:
                if r[0] == realname:
                    result[result.index(r)] = (nickname, r[1], r[2])
    return result, zhiban, allPerson(), repeatedly


def getNicknameByRealname(realname):
    '''
    根据真实姓名获取昵称
    :param realname: str - 真实姓名
    :return: str - 昵称或原姓名（如果未找到）
    '''
    conn = sqlite3.connect('./data/diancan.db')
    cursor = conn.cursor()
    query = "SELECT nickname FROM realname WHERE realname = ?"
    cursor.execute(query, (realname, ))
    result = cursor.fetchone()
    conn.close()
    if result:
        return result[0]
    else:
        return realname


def getRealnameByNickname(nickname):
    '''
    根据昵称获取真实姓名
    :param nickname: str - 昵称
    :return: str - 真实姓名或None（如果未找到）
    '''
    conn = sqlite3.connect('./data/diancan.db')
    cursor = conn.cursor()
    query = "SELECT realname FROM realname WHERE nickname = ?"
    cursor.execute(query, (nickname, ))
    result = cursor.fetchone()
    conn.close()
    if result:
        return result[0]
    else:
        return None


def read_excel(file_path, sheet_name=0):
    """
    读取Excel文件并返回数据
    :param file_path: str - Excel文件路径
    :param sheet_name: str/int - 要读取的工作表名称或索引，默认为第一个工作表
    :return: list - 包含Excel数据的列表
    """
    try:
        data = pd.read_excel(file_path, sheet_name=sheet_name).fillna('')
        return data.values.tolist()  # 返回数据的列表形式
    except Exception as e:
        print(f"读取Excel文件时出错: {e}")
        return None


def zhibanbiao(target_date):
    '''
    根据值班表Excel文件预测指定日期的值班人员
    :param target_date: str - 目标日期（格式：%Y年%m月%d日）
    :return: list - 值班人员列表
    '''
    data = read_excel('./data/zhiban.xlsx')
    date1st = data[0][0]
    # print(data)
    # def predict_names(target_date):
    # 解析目标日期

    target = datetime.strptime(target_date, '%Y年%m月%d日')
    start = datetime.strptime(date1st, '%Y年%m月%d日')
    # 计算天数差
    delta = (target - start).days
    cycle_pos = delta % len(data)  # 模5循环

    # 返回对应的名字
    return data[cycle_pos][1:]


def allzhibanBiao():
    '''
    获取所有值班人员信息
    '''
    data = read_excel('./data/zhibanall.xlsx')
    date1st = data
    print(date1st)


def allPerson():
    '''
    获取所有人员信息
    :return: dict - 所有人员信息字典
    '''
    with open("./config/zhiban.json", 'r', encoding='utf-8') as f:
        allPerson = json.load(f)
    return allPerson


def count_diancan(start, end):
    '''
    统计指定时间范围内的早、午、晚餐次数
    只统计包含早、午、晚，但不包含"不"和"人"的记录
    :param start: int - 开始时间戳
    :param end: int - 结束时间戳
    :return: tuple - (早餐次数, 午餐次数, 晚餐次数, 总次数)
    '''
    breakfast_count = 0
    lunch_count = 0
    dinner_count = 0
    conn = sqlite3.connect('./data/diancan.db')
    cursor = conn.cursor()
    query = "SELECT diancan FROM diancan WHERE time >= ? AND time <= ?"
    cursor.execute(query, (start, end))
    res = cursor.fetchall()
    conn.close()
    if res:
        for r in res:
            if r[0]:
                # 只统计包含早、午、晚，但不包含"不"和"人"的记录
                if ('早' in r[0] and '不' not in r[0] and '人' not in r[0]):
                    breakfast_count += 1
                elif ('午' in r[0] and '不' not in r[0] and '人' not in r[0]):
                    lunch_count += 1
                elif ('晚' in r[0] and '不' not in r[0] and '人' not in r[0]):
                    dinner_count += 1
    return breakfast_count, lunch_count, dinner_count, breakfast_count + lunch_count + dinner_count


def send_notification():
    """
    发送点餐统计通知给订阅用户
    通过微信模板消息发送前一天的点餐统计信息
    """
    start, end = get_start_and_end_of_day(int(time.time() - 86400))
    breakfast_count, lunch_count, dinner_count, count = count_diancan(
        start, end)
    conn = sqlite3.connect('./data/users.db')
    cursor = conn.cursor()
    # 获取所有订阅招标的用户openid
    cursor.execute(
        "SELECT openid FROM users WHERE template_id LIKE '%Kk05z69xuSB3bNo075Nw56lwa6hzpyWQLj61eWMMPYE%'"  # 订阅招标的模板id
    )
    openid_list = cursor.fetchall()
    conn.close()
    import diancan
    zao, zhong, wan = diancan.checkWxRecord(start, end)
    if openid_list:
        notification_time = time.strftime("%Y年%m月%d日",
                                          time.localtime(time.time() - 86400))

        for openid in openid_list:
            th = Thread(
                target=wechatsubscribe.send_notification,
                args=
                (openid[0], "点餐",
                 (f'早{breakfast_count}午{lunch_count}晚{dinner_count}', count,
                  notification_time),
                 f"pages/renyuan/renyuan?query={notification_time}&diff={zao,breakfast_count,zhong,lunch_count,wan,dinner_count}"
                 ),
                daemon=True)
            th.start()
            th.join()
    # print(f"Notification - {title}: {message}")


def schedule_daily_notification():
    """
    安排每日9点00分发送前一天通知
    """
    while True:
        # 获取当前时间
        now = datetime.now()
        # 计算下次运行时间（今天或明天的 09:00 ）
        next_run = now.replace(hour=9, minute=0, second=0, microsecond=0)
        if now > next_run:
            # 如果当前时间已过今日00:01，则设置为明天00:01
            next_run += timedelta(days=1)

        # 计算等待时间
        wait_time = (next_run - now).total_seconds()
        print(f"下次发送通知时间: {next_run.strftime('%Y-%m-%d %H:%M:%S')}")

        # 等待到下次运行时间
        time.sleep(wait_time)

        # 发送通知
        try:
            send_notification()
            print("通知已发送")
        except Exception as e:
            print(f"发送通知时出错: {e}")

        # 等待一段时间以避免重复触发
        time.sleep(60)


def getCountByRealname(nickname):
    '''
    根据昵称获取当月点餐次数统计
    :param nickname: str - 用户昵称
    :return: tuple - (早餐次数, 午餐次数, 晚餐次数, 总次数)
    '''
    breakfast_count = 0
    lunch_count = 0
    dinner_count = 0
    # 获取当月首日时间戳
    now = datetime.now()
    start_month_timestamp = int(datetime(now.year, now.month, 1).timestamp())
    # 获取当月末日时间戳
    if now.month == 12:
        end_month_timestamp = int(datetime(now.year + 1, 1, 1).timestamp()) - 1
    else:
        end_month_timestamp = int(
            datetime(now.year, now.month + 1, 1).timestamp()) - 1

    conn = sqlite3.connect('./data/diancan.db')
    cursor = conn.cursor()
    query = "SELECT diancan FROM diancan WHERE name = ? AND time BETWEEN ? AND ?"
    cursor.execute(query,
                   (nickname, start_month_timestamp, end_month_timestamp))
    res = cursor.fetchall()
    conn.close()
    print(res)
    if res:
        for r in res:
            if r[0]:
                # 只统计包含早、午、晚，但不包含"不"和"人"的记录
                if ('早' in r[0] and '不' not in r[0] and '人' not in r[0]):
                    breakfast_count += 1
                elif ('午' in r[0] and '不' not in r[0] and '人' not in r[0]):
                    lunch_count += 1
                elif ('晚' in r[0] and '不' not in r[0] and '人' not in r[0]):
                    dinner_count += 1
    return breakfast_count, lunch_count, dinner_count, breakfast_count + lunch_count + dinner_count


def checkWxRecord(start_time=0, end_time=0):
    bf = 0
    lc = 0
    din = 0
    # print(start_time, end_time)
    if (start_time == 0):
        start_time, end_time = get_start_and_end_of_day(
            now_time=int(time.time()))
    else:
        try:
            start_time = date_to_timestamp(start_time,
                                           hour=0,
                                           minute=0,
                                           second=0)
            end_time = date_to_timestamp(end_time,
                                         hour=23,
                                         minute=59,
                                         second=59)
        except Exception as e:
            print(e)
        # end_time = get_start_and_end_of_day()
    print(start_time, end_time)
    conn = sqlite3.connect("./data/diancan.db")
    cursor = conn.cursor()
    cursor.execute(
        "SELECT * FROM weixinrecord WHERE timestamp >= ? AND timestamp <= ?",
        (start_time, end_time))
    result = cursor.fetchall()
    # print(result)
    conn.close()
    if result:
        for record in result:  # 遍历结果列表
            # record[2] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(record[2]))
            # print(record)
            if '早' in record[1] and (not '不' in record[1]
                                     and not '人' in record[1]):
                bf += 1
            elif '午' in record[1] and (not '不' in record[1]
                                       and not '人' in record[1]):
                # print(record[1])
                lc += 1
            elif '晚' in record[1] and (not '不' in record[1]
                                       and not '人' in record[1]):
                din += 1
    return bf, lc, din


def loop():
    '''
    主循环函数，启动定时任务线程
    '''
    # 启动定时任务线程
    notification_thread = Thread(target=schedule_daily_notification,
                                 daemon=True)
    notification_thread.start()

    # 保持主线程运行
    try:
        while True:
            time.sleep(1)
    except KeyboardInterrupt:
        print("程序已退出")

if __name__ == "__main__":
    # send_notification()
    from mqttser import mqtt_run
    Thread(target=mqtt_run, args=('点餐统计', ("software", 0)),
           daemon=True).start()
    # import checknet
    # Thread(target=checknet.main, daemon=True).start()
    # th2.start()
    loop()
